package com.personal.dataconvert;

import java.beans.PropertyDescriptor;
import java.io.ByteArrayOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.personal.core.bean.CountEntity;
import com.personal.core.data.DataRow;
import com.personal.core.data.DataTable;
import com.personal.core.utils.Assert;
import com.personal.core.utils.CoreUtil;
import com.personal.core.utils.ReGularUtil;
import com.personal.dataconvert.bean.DataConvertStyle;
import com.personal.dataconvert.bean.HeaderConfig;
import com.personal.dataconvert.bean.SheetConfig;
import com.personal.dataconvert.bean.WorkBookConfig;
import com.personal.dataconvert.port.Data2Excel;
import com.personal.dataconvert.util.CombineDataTableUtil;
import com.personal.dataconvert.util.ExcelHtmlUtil;
import com.personal.dataconvert.util.ExportExcelConfigApp;

/**
 * 将数据根据配置导出至Excel
 * @author cuibo
 */
public class ExportData2Excel implements Data2Excel
{

	/** 所有的数据 key：SheetName value:每个Sheet的数据集 */
	private Map<String, List<Map<String, Object>>> allData = null;

	/** 所有的数据 key：SheetName value:每个Sheet的数据集 */
	private Map<String, List<?>> allObjectData = null;

	/** 配置ID */
	private String configId;

	// 居中样式
	private CellStyle dataCellStyleCenter = null;

	// 左对其样式
	private CellStyle dataCellStyleLeft = null;

	// 右对齐样式
	private CellStyle dataCellStyleRight = null;

	// Excel 类型
	private String excelType;

	// 红色字体
	private Font redfont = null;

	// 正常黑色字体
	private Font bodyfont = null;

	/** 样式缓存 */
	private Map<String, CellStyle> cellStyleCache = new HashMap<String, CellStyle>();

	// 头部样式
	private CellStyle headerCellStyle = null;

	// 左页眉
	private CellStyle leftYmCellStyle = null;

	// 右页眉
	private CellStyle rightYmCellStyle = null;

	// 缓存行号已经遍历到的列数
	private Map<Integer, Integer> rowColMap = new HashMap<Integer, Integer>();

	// 标题样式
	private CellStyle titileCellStyle = null;

	// workBook
	private Workbook workbook;

	/** 配置信息 */
	private WorkBookConfig workBookConfig;

	private ExportData2Excel()
	{

	}

	/**
	 * 由构建器构建本身信息
	 * @param builder
	 */
	private ExportData2Excel(Builder builder)
	{
		this.configId = builder.configId;
		this.workBookConfig = builder.workBookConfig;
		this.allObjectData = builder.allObjectData;
		this.workbook = builder.workbook;
		this.excelType = builder.excelType;
		convertObject(allObjectData);
	}

	/**
	 * DataSet转Excel
	 * @param ds
	 * @return
	 * @throws Exception
	 */
	@Override
	public byte[] exportExcel() throws Exception
	{
		fillExcel();
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		workbook.write(out);
		return out.toByteArray();
	}

	/**
	 * DataSet转Excel
	 * @param ds
	 * @return
	 * @throws Exception
	 */
	@Override
	public Workbook fillExcel() throws Exception
	{
		if (workBookConfig == null)
		{
			// 通过配置ID去获取
			workBookConfig = ExportExcelConfigApp.getWorkBookConfig(configId);
		}
		Assert.isNotNull(workBookConfig, "获取下载配置失败！");
		init();
		// 转换数据
		converData();
		return workbook;
	}

	/**
	 * 处理其他行的开始列
	 * @param row
	 * @param rowspan
	 * @param colspan
	 */
	private void addOtherRowColNum(int row, int rowspan, int colspan)
	{
		for (int i = row + 1; i < row + 1 + rowspan - 1; i++)
		{
			Integer col = rowColMap.get(i);
			if (col == null)
			{
				// 加1是因为记录下次开始的位置
				rowColMap.put(i, colspan + 1);
			}
			else
			{
				rowColMap.put(i, rowColMap.get(i) + colspan);
			}
		}
	}

	/**
	 * 计算每个HeaderNode 的行，列，跨行，跨列信息
	 * @param headerNodes
	 * @param leafNodes
	 * @throws Exception
	 */
	private void calHeaderNodeDetail(SheetConfig sheetConfig, List<? extends HeaderConfig> headerNodes, List<HeaderConfig> leafNodes) throws Exception
	{
		// 跨行数
		int rowspan = 0;
		// 跨列数
		int colspan = 0;
		// 最大深度差
		int maxDeepLength = 0;
		// 每行的节点
		List<HeaderConfig> rowNodes = null;
		// 直接递归循环,求出每行的应该开始列数(下标均从1开始)
		for (HeaderConfig headerNode : headerNodes)
		{
			// 2016 10 28 提供是否显示选择
			if (!headerNode.isDisplay())
			{
				continue;
			}
			if (rowColMap.get(headerNode.getDeepLength()) == null)
			{
				rowColMap.put(headerNode.getDeepLength(), 1);
			}
			colspan = headerNode.getMaxWidth(new CountEntity());
			// 设置列
			headerNode.setColIndex(rowColMap.get(headerNode.getDeepLength()) - 1);
			// 设置行
			headerNode.setRowIndex(headerNode.getDeepLength() - 1);
			// 设置跨列
			headerNode.setColspan(colspan);

			// 当前行号遍历到的列号需要加上当前headerNode所占的列
			rowColMap.put(headerNode.getDeepLength(), rowColMap.get(headerNode.getDeepLength()) + colspan);
			// 获取指定深度的所有数据
			rowNodes = ExcelHtmlUtil.getHeaderConfigsByDeepLength(headerNode.getDeepLength(), headerNode.getSheetConfig().getHeaderConfigs());
			// 相同深度节点的深度最大值
			rowspan = ExcelHtmlUtil.getMaxDeepLength(rowNodes, headerNode);
			// 当前节点的深度差
			maxDeepLength = headerNode.getMaxDeepLength();
			// 如果存在跨行信息，要把它所跨的行所在的列数加上自己所占的列数
			// 跨行数。如果当前节点距离子节点中的最大深度差大于等于其相邻的最大深度差,并且其深度差大于0 ，则不跨行
			if (maxDeepLength < rowspan && maxDeepLength == 0)
			{
				// 存在跨行
				addOtherRowColNum(headerNode.getDeepLength(), rowspan - maxDeepLength + 1, colspan);
				// 设置跨行
				headerNode.setRowspan(rowspan - maxDeepLength + 1);
			}
			else
			{
				// 设置跨行
				headerNode.setRowspan(1);
			}
			if (headerNode.getChildren().size() > 0)
			{
				calHeaderNodeDetail(sheetConfig, headerNode.getChildren(), leafNodes);
			}
			else
			{
				// 2016 10 28 提供是否显示的配置
				if (headerNode.isDisplay())
				{
					leafNodes.add(headerNode);
				}
			}
		}
	}

	private void converData() throws Exception
	{
		for (SheetConfig sheetConfig : workBookConfig.getSheetConfigs())
		{
			convertSheetData(sheetConfig, allData == null ? null : allData.get(sheetConfig.getName()));
		}
	}

	@SuppressWarnings("unchecked")
	private void convertObject(Map<String, List<?>> allObjectData)
	{
		if (allObjectData == null || allObjectData.isEmpty())
		{
			return;
		}
		allData = new HashMap<String, List<Map<String, Object>>>();
		Map<String, Object> map = null;
		for (Entry<String, List<?>> entry : allObjectData.entrySet())
		{
			if (entry.getValue() == null || entry.getValue().isEmpty())
			{
				allData.put(entry.getKey(), null);
				continue;
			}
			allData.put(entry.getKey(), new ArrayList<Map<String, Object>>());
			for (Object object : entry.getValue())
			{
				if (object instanceof Map<?, ?>)
				{
					map = (Map<String, Object>) object;
					allData.get(entry.getKey()).add(map);
				}
				else
				{
					// 获取 object的所有属性
					map = loadObjectFieldData(object);
					allData.get(entry.getKey()).add(map);
				}
			}
		}
	}

	private Map<String, Object> loadObjectFieldData(Object object)
    {
	    PropertyDescriptor pd = null;
        Method method = null;
        Map<String, Object> result = new HashMap<String, Object>();
        for (Field field : object.getClass().getDeclaredFields())
        {
            try
            {
                pd = new PropertyDescriptor(field.getName(), object.getClass());
            }
            catch (Exception e)
            {
                // 对于不能获取的字段，如 serialVersionUID 则不处理
                continue;
            }
            method = pd.getReadMethod();
            if (method != null)
            {
                try
                {
                    result.put(field.getName(), method.invoke(object));
                }
                catch (IllegalArgumentException e)
                {
                    e.printStackTrace();
                }
                catch (IllegalAccessException e)
                {
                    e.printStackTrace();
                }
                catch (InvocationTargetException e)
                {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    /**
	 * 转换Sheet数据
	 * @param sheetConfig
	 * @param sheetData
	 * @throws Exception
	 */
	private void convertSheetData(SheetConfig sheetConfig, List<Map<String, Object>> sheetData) throws Exception
	{
		// 配置信息为空，则不导出
		if (sheetConfig == null || CoreUtil.isEmpty(sheetConfig.getName()))
		{
			return;
		}
		String sheetName = ExcelHtmlUtil.handleExcelSheetName(sheetConfig.getName());
		// 重置缓存
		rowColMap.clear();
		// 创建Sheet页
		Sheet sheet = workbook.getSheet(sheetName);
		if (sheet == null)
		{
			sheet = workbook.createSheet(sheetName);
		}
		// 是否需要重新排序
		if (sheetConfig.getIndex() != -1)
		{
			workbook.setSheetOrder(sheetName, sheetConfig.getIndex());
		}
		int maxCol = 0;
		// 2017 08 23 兼容不规则表
		if (sheetConfig.isIrregular())
		{
			DataTable irregularTable = CombineDataTableUtil.createIrregularTable(sheetConfig.getName(), sheetData);
			maxCol = fillIRegularBodyDataImpl(sheetConfig, sheet, irregularTable);
			// 设置列宽，统一设计为固定宽度
			if (maxCol > 0)
			{
				for (int i = 0; i < maxCol; i++)
				{
					sheet.setColumnWidth(i, ExcelHtmlUtil.IRREGULAREXCELWIDTH);
				}
			}
		}
		else
		{
			List<? extends HeaderConfig> headerNodes = sheetConfig.getHeaderConfigs();
			if (headerNodes == null || headerNodes.isEmpty())
			{
				return;
			}
			// 设置深度等信息，防止使用者传过来非法数据
			ExcelHtmlUtil.setSheetConfigAndDeepLength(sheetConfig, headerNodes);
			// 计算每个HeaderConfig 的行，列，跨行，跨列信息,并返回所有叶子节点
			// 所有的叶子节点
			List<HeaderConfig> leafConfigs = new ArrayList<HeaderConfig>();
			calHeaderNodeDetail(sheetConfig, headerNodes, leafConfigs);

			maxCol = leafConfigs.size();
			// 创建表头才考虑这些
			if (sheetConfig.isCreateHeader())
			{
				// 如果有title，页眉则处理
				fillTitle(sheet, sheetConfig, maxCol);
				// 2016 10 17 考虑追加的情况
				int lastRowNum = getNextStartRowNum(sheet);
				// 由表头创建Excel表头
				createRegularHeader(headerNodes, sheet, lastRowNum);
				// 合并表头
				megreRegularHeader(headerNodes, sheet, lastRowNum);
			}
			// 设置列宽
			setColWidth(sheet, leafConfigs);
			// 没有设置合并配置则认为是规则表
			if (sheetConfig.getCombineConfigs() == null || sheetConfig.getCombineConfigs().isEmpty())
			{
				// 填充表体
				fillRegularBodyData(sheetConfig, sheet, leafConfigs, sheetData);
			}
			else
			{
				// 填充不规则表表体
				fillIRegularBodyData(sheetConfig, sheet, leafConfigs, sheetData);
			}
		}
		// 2017 09 28 新增页脚功能
		if (!CoreUtil.isEmpty(sheetConfig.getPagefotter()))
		{
			appendPageFotter(sheetConfig, sheet, maxCol);
		}
		// 2017 10 23 新增合单元格功能
		if (!CoreUtil.isEmpty(sheetConfig.getRangeAddresses()))
        {
		    rangeCellByAddresses(sheet, sheetConfig.getRangeAddresses());
        }
	}

	/**
	 * 合并单元格
	 * @param sheet
	 * @param rangeAddresses
	 */
	private void rangeCellByAddresses(Sheet sheet, List<CellRangeAddress> rangeAddresses)
    {
        for (CellRangeAddress cellRangeAddress : rangeAddresses)
        {
            // 合并大类别和小类别
            Row row = sheet.getRow(cellRangeAddress.getFirstRow());
            if (row == null)
            {
                continue;
            }
            Cell cell = row.getCell(cellRangeAddress.getFirstColumn());
            if (cell == null)
            {
                continue;
            }
            CellStyle headerCellStyle = cell.getCellStyle();
            sheet.addMergedRegion(cellRangeAddress);
            // 设置合并单元格样式
            ExcelHtmlUtil.setBodyStyle(headerCellStyle, cellRangeAddress, sheet);
        }
    }

    /**
	 * 追加页脚
	 * @param sheetConfig
	 * @param sheet
	 * @param maxCol
	 */
	private void appendPageFotter(SheetConfig sheetConfig, Sheet sheet, int maxCol)
	{
		// 根据内容的换行数来决定行高（不精准）
		int rowNum = sheetConfig.getPagefotter().split("\n").length;
		int nextRowNum = getNextStartRowNum(sheet);
		// 页脚垮所有列
		Row newRow = sheet.getRow(nextRowNum);
		if (newRow == null)
		{
			newRow = sheet.createRow(nextRowNum);
		}
		newRow.setHeight((short) (rowNum * newRow.getHeight()));
		Cell newCell = newRow.getCell(0);
		if (newCell == null)
		{
			newCell = newRow.createCell(0);
		}
		newCell.setCellValue(sheetConfig.getPagefotter());
		newCell.setCellType(Cell.CELL_TYPE_STRING);
		newCell.setCellStyle(dataCellStyleLeft);
		// 合并
		CellRangeAddress cra = new CellRangeAddress(nextRowNum, nextRowNum, 0, maxCol - 1);
		sheet.addMergedRegion(cra);
		setBodyStyle(dataCellStyleLeft, cra, sheet);
	}

	/**
	 * 填充规则表表体
	 * @param sheetConfig
	 * @param sheet
	 * @param leafConfigs
	 * @param sheetData
	 * @throws Exception
	 */
	private void fillIRegularBodyData(SheetConfig sheetConfig, Sheet sheet, List<HeaderConfig> leafConfigs, List<Map<String, Object>> sheetData) throws Exception
	{
		// 由数据构建DataTable然后合并
		DataTable iregularDataTable = CombineDataTableUtil.combineDataTable(leafConfigs, sheetData, sheetConfig.getCombineConfigs());
		fillIRegularBodyDataImpl(sheetConfig, sheet, iregularDataTable);
	}

	/**
	 * 填充规则表表体Impl
	 * @param sheetConfig
	 * @param sheet
	 * @param iregularDataTable
	 * @return 最大列号
	 */
	private int fillIRegularBodyDataImpl(SheetConfig sheetConfig, Sheet sheet, DataTable table)
	{
		// 2016 10 17 考虑追加的情况
		int headerRowNum = getNextStartRowNum(sheet);
		// 冻结表头
		if (sheetConfig.isFreezeHeader())
		{
			sheet.createFreezePane(sheetConfig.getFreezeColIndex() == -1 ? 0 : sheetConfig.getFreezeColIndex() + 1, headerRowNum);
		}
		else
		{
			sheet.createFreezePane(sheetConfig.getFreezeColIndex() == -1 ? 0 : sheetConfig.getFreezeColIndex() + 1, 0);
		}
		if (!CoreUtil.checkDataTableHasData(table))
		{
			return -1;
		}
		// 填充数据
		int rowIndex = 0;
		int colIndex = 0;
		Row sheetRow = null;
		Cell cell = null;
		// 最大列数
		int maxCol = 0;
		for (DataRow row : table.getRows())
		{
			rowIndex = CoreUtil.parseInt(row.getItemMap().get(ExcelHtmlUtil.ROWINDEX)) + headerRowNum;
			colIndex = CoreUtil.parseInt(row.getItemMap().get(ExcelHtmlUtil.COLINDEX));
			if (colIndex > maxCol)
			{
				maxCol = colIndex;
			}
			sheetRow = sheet.getRow(rowIndex);
			if (sheetRow == null)
			{
				sheetRow = sheet.createRow(rowIndex);
			}
			cell = sheetRow.getCell(colIndex);
			if (cell == null)
			{
				cell = sheetRow.createCell(colIndex);
			}
			if (CoreUtil.isNumber(row.getItemMap().get(ExcelHtmlUtil.DATA)) && ReGularUtil.NUMBER_PATTERN.matcher(CoreUtil.parseStr(row.getItemMap().get(ExcelHtmlUtil.DATA))).matches())
			{
				cell.setCellValue(CoreUtil.parseDbl(row.getItemMap().get(ExcelHtmlUtil.DATA)));
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			}
			else if (row.getItemMap().get(ExcelHtmlUtil.DATA) != null && row.getItemMap().get(ExcelHtmlUtil.DATA) instanceof Date)
			{
				// 兼容时间类型的格式
				cell.setCellValue(CoreUtil.formatDate(row.getItemMap().get(ExcelHtmlUtil.DATA)));
				cell.setCellType(Cell.CELL_TYPE_STRING);
			}
			else
			{
				cell.setCellValue(CoreUtil.parseStr(row.getItemMap().get(ExcelHtmlUtil.DATA)));
			}
			// 设置样式
			cell.setCellStyle(getCellStyle(CoreUtil.parseStr(row.getItemMap().get(ExcelHtmlUtil.ALIGN)), null, CoreUtil.parseStr(row.getItemMap().get(ExcelHtmlUtil.EXCELSTYLE))));
			// 2017 03 22 新增备注的支持
			if (!CoreUtil.isEmpty(row.getItemMap().get(ExcelHtmlUtil.POSTIL)))
			{
				createCellPostil(sheet, cell, CoreUtil.parseStr(row.getItemMap().get(ExcelHtmlUtil.POSTIL)));
			}
		}
		// 合并单元格
		CellRangeAddress cra = null;
		int rowSpan = 0;
		int colSpan = 0;

		for (DataRow row : table.getRows())
		{
			rowSpan = CoreUtil.parseInt(row.getItemMap().get(ExcelHtmlUtil.MERGEROWCOUNT));
			colSpan = CoreUtil.parseInt(row.getItemMap().get(ExcelHtmlUtil.MERGECOLCOUNT));

			// 调整
			rowIndex = CoreUtil.parseInt(row.getItemMap().get(ExcelHtmlUtil.ROWINDEX)) + headerRowNum;
			colIndex = CoreUtil.parseInt(row.getItemMap().get(ExcelHtmlUtil.COLINDEX));
			if (rowSpan > 1 || colSpan > 1)
			{
				cra = new CellRangeAddress(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex + colSpan - 1);
				sheet.addMergedRegion(cra);
				setBodyStyle(getCellStyle(CoreUtil.parseStr(row.getItemMap().get(ExcelHtmlUtil.ALIGN)), null, CoreUtil.parseStr(row.getItemMap().get(ExcelHtmlUtil.EXCELSTYLE))), cra, sheet);
			}
		}
		return maxCol;
	}

	/**
	 * 填充规则表表体
	 * @param sheetConfig
	 * @param sheet
	 * @param leafConfigs
	 * @param sheetDatas
	 */
	private void fillRegularBodyData(SheetConfig sheetConfig, Sheet sheet, List<HeaderConfig> leafConfigs, List<Map<String, Object>> sheetDatas)
	{
		// 2016 10 17 考虑追加的情况
		int startRowNum = getNextStartRowNum(sheet);
		// 冻结表头
		if (sheetConfig.isFreezeHeader())
		{
			sheet.createFreezePane(sheetConfig.getFreezeColIndex() == -1 ? 0 : sheetConfig.getFreezeColIndex() + 1, startRowNum);
		}
		else
		{
			sheet.createFreezePane(sheetConfig.getFreezeColIndex() == -1 ? 0 : sheetConfig.getFreezeColIndex() + 1, 0);
		}
		if (sheetDatas == null || sheetDatas.isEmpty())
		{
			return;
		}
		Row newRow = null;
		Cell newCell = null;

		int index = 0;
		int rowIndex = 0;
		DateFormat dateFormat = null;
		// 行高
		int height = workBookConfig.getStyleConfig().getBodyHeight();
		for (Map<String, Object> map : sheetDatas)
		{
			newRow = sheet.getRow(startRowNum);
			if (newRow == null)
			{
				// 创建行
				newRow = sheet.createRow(startRowNum);
				// 如果指定了行高，则设置
				if (height > 0)
				{
					newRow.setHeight((short) height);
				}
			}
			index = 0;
			for (HeaderConfig config : leafConfigs)
			{
				newCell = newRow.getCell(index);
				if (newCell == null)
				{
					newCell = newRow.createCell(index);
				}
				// cb 2016 12 05 增加value配置自增的情况
				if (HeaderConfig.ZZ.equals(config.getValue()))
				{
					newCell.setCellValue("" + (++rowIndex));
					newCell.setCellType(Cell.CELL_TYPE_STRING);
				}
				else
				{
					// 是数字并且数据类型不是字符串类型才转成数字
					if (CoreUtil.isNumber(map.get(config.getValue())) && !HeaderConfig.STRING.equals(config.getDataType()) && ReGularUtil.NUMBER_PATTERN.matcher(CoreUtil.parseStr(map.get(config.getValue()))).matches())
					{
						newCell.setCellValue(CoreUtil.parseDbl(map.get(config.getValue())));
						newCell.setCellType(Cell.CELL_TYPE_NUMERIC);
					}
					else if (map.get(config.getValue()) != null && map.get(config.getValue()) instanceof Date)
					{
						// 兼容时间类型的格式，2017 1 18 新增时间格式化的配置
						if (CoreUtil.isEmpty(config.getFormatInfo()))
						{
							newCell.setCellValue(CoreUtil.formatDate(map.get(config.getValue())));
						}
						else
						{
							dateFormat = ExcelHtmlUtil.getDateFormat(config.getFormatInfo());
							if (dateFormat != null)
							{
								newCell.setCellValue(dateFormat.format(map.get(config.getValue())));
							}
							else
							{
								newCell.setCellValue("");
							}
						}
						newCell.setCellType(Cell.CELL_TYPE_STRING);
					}
					else
					{
						newCell.setCellValue(CoreUtil.parseStr(map.get(config.getValue())));
						newCell.setCellType(Cell.CELL_TYPE_STRING);
					}
				}
				// 设置样式
				// 根据DataColumn中来决定样式
				setCellStyle(newCell, config.getAlign(), CoreUtil.parseStr(map.get(TRSTYLE)), CoreUtil.parseStr(map.get(config.getValue() + TDSTYLE)));
				// cb 2017 03 22 新增td上添加备注的功能
				if (!CoreUtil.isEmpty(map.get(config.getValue() + TDPOSTIL)))
				{
					createCellPostil(sheet, newCell, CoreUtil.parseStr(map.get(config.getValue() + TDPOSTIL)));
				}
				index++;
			}
			// 隐藏行
			if (Boolean.valueOf(CoreUtil.parseStr(map.get(Data2Excel.TRHIDE))))
			{
				newRow.setZeroHeight(true);
			}
			startRowNum++;
		}
	}

	/**
	 * 创建单元格备注
	 * @param sheet
	 * @param newCell
	 * @param postil
	 */
	private void createCellPostil(Sheet sheet, Cell newCell, String postil)
	{
		// 1.得到一个POI的工具类
		CreationHelper factory = workbook.getCreationHelper();
		// 2.得到一个换图的对象
		Drawing drawing = sheet.createDrawingPatriarch();
		// 3. ClientAnchor是附属在WorkSheet上的一个对象， 其固定在一个单元格的左上角和右下角.
		ClientAnchor anchor = factory.createClientAnchor();
		// 6.2. 对这个单元格加上注解
		Comment comment = drawing.createCellComment(anchor);
		RichTextString text = factory.createRichTextString(postil);
		comment.setString(text);
		comment.setAuthor("Apache POI");
		newCell.setCellComment(comment);
	}

	/**
	 * 设置单元格样式
	 * @param cell
	 * @param textAlign 对其方式
	 * @param trStyle 行样式
	 * @param tdStyle 单元格样式
	 */
	private void setCellStyle(Cell cell, String textAlign, String trStyle, String tdStyle)
	{
		cell.setCellStyle(getCellStyle(textAlign, trStyle, tdStyle));
	}

	/**
	 * 获取CellStyle
	 * @param textAlign
	 * @param trStyle
	 * @param tdStyle
	 * @return
	 */
	public CellStyle getCellStyle(String textAlign, String trStyle, String tdStyle)
	{
		if (CoreUtil.isEmpty(trStyle) && CoreUtil.isEmpty(tdStyle))
		{
			if (HeaderConfig.LEFT.equals(textAlign))
			{
				return dataCellStyleLeft;
			}
			else if (HeaderConfig.RIGHT.equals(textAlign))
			{
				return dataCellStyleRight;
			}
			else
			{
				return dataCellStyleCenter;
			}
		}
		else
		{
			if (CoreUtil.isEmpty(trStyle))
			{
				trStyle = "";
			}
			else
			{
				trStyle = trStyle.replaceAll("\\s", "").toUpperCase();
			}
			if (CoreUtil.isEmpty(tdStyle))
			{
				tdStyle = "";
			}
			else
			{
				tdStyle = tdStyle.replaceAll("\\s", "").toUpperCase();
			}
			if (CoreUtil.isEmpty(textAlign))
			{
				textAlign = "";
			}
			else
			{
				textAlign = textAlign.replaceAll("\\s", "").toUpperCase();
			}
			// tdStyle > trStyle
			String style = CoreUtil.isEmpty(tdStyle) ? textAlign + trStyle : textAlign + tdStyle;
			if (cellStyleCache.containsKey(style))
			{
				return cellStyleCache.get(style);
			}
			CellStyle newStyle = workbook.createCellStyle();
			if (HeaderConfig.LEFT.equalsIgnoreCase(textAlign))
			{
				newStyle.cloneStyleFrom(dataCellStyleLeft);
			}
			else if (HeaderConfig.RIGHT.equalsIgnoreCase(textAlign))
			{
				newStyle.cloneStyleFrom(dataCellStyleRight);
			}
			else
			{
				newStyle.cloneStyleFrom(dataCellStyleCenter);
			}
			// 字体样式
			if (DataConvertStyle.isFontRed(style))
			{
				newStyle.setFont(redfont);
			}
			// 背景颜色
			if (DataConvertStyle.isPinkBackGround(style))
			{
				newStyle.setFillForegroundColor(HSSFColor.PINK.index);
				newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			}
			else if (DataConvertStyle.isRedBackGround(style))
			{
				newStyle.setFillForegroundColor(HSSFColor.RED.index);
				newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			}
			else if (DataConvertStyle.isYellowBackGround(style))
			{
				newStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
				newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			}
			else if (DataConvertStyle.isNxPinkBackGround(style))
			{
				// 宁夏颜色特殊照顾
				HSSFColor color = ExcelHtmlUtil.getColor(workbook, (byte) 248, (byte) 173, (byte) 173);
				if (color != null)
				{
					newStyle.setFillForegroundColor(color.getIndex());
					newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
				}
			}
			else if (DataConvertStyle.isHnYellowBackGround(style))
			{
				// 海南颜色特殊照顾
				HSSFColor color = ExcelHtmlUtil.getColor(workbook, (byte) 255, (byte) 227, (byte) 199);
				if (color != null)
				{
					newStyle.setFillForegroundColor(color.getIndex());
					newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
				}
			}
			cellStyleCache.put(style, newStyle);
			return newStyle;
		}
	}

	/**
	 * 创建规则表表头
	 * @param headerNodes
	 * @param sheet
	 * @param lastRowNum
	 */
	private void createRegularHeader(List<? extends HeaderConfig> headerNodes, Sheet sheet, int lastRowNum)
	{
		if (headerNodes == null || headerNodes.isEmpty())
		{
			return;
		}
		Row newRow = null;
		Cell newCell = null;

		int headerHight = workBookConfig.getStyleConfig().getHeaderHeight();
		for (HeaderConfig headerNode : headerNodes)
		{
			// 2016 10 28 提供是否显示选择
			if (!headerNode.isDisplay())
			{
				continue;
			}
			newRow = sheet.getRow(headerNode.getRowIndex() + lastRowNum);
			if (newRow == null)
			{
				newRow = sheet.createRow(headerNode.getRowIndex() + lastRowNum);
				// 如果设置了表头高度
				if (headerHight > 0)
				{
					newRow.setHeight((short) headerHight);
				}
			}
			newCell = newRow.getCell(headerNode.getColIndex());
			if (newCell == null)
			{
				newCell = newRow.createCell(headerNode.getColIndex());
			}
			newCell.setCellValue(CoreUtil.parseStr(headerNode.getDisplayName()));
			newCell.setCellStyle(headerCellStyle);
			if (headerNode.getChildren().size() > 0)
			{
				createRegularHeader(headerNode.getChildren(), sheet, lastRowNum);
			}
		}
	}

	/**
	 * 处理title和页眉
	 * @param sheet
	 * @param sheetConfig
	 * @param size 最大列数
	 */
	private void fillTitle(Sheet sheet, SheetConfig sheetConfig, int size)
	{
		// 2016 10 17 考虑追加的情况
		int lastRowNum = getNextStartRowNum(sheet);
		Row newRow = null;
		Cell newCell = null;
		CellRangeAddress cra = null;
		// 如果指定表头占多行则需处理
		int titleRowCount = 1;
		if (!CoreUtil.isEmpty(sheetConfig.getTitle()))
		{
			newRow = sheet.createRow(lastRowNum);
			newCell = newRow.createCell(0);
			newCell.setCellValue(sheetConfig.getTitle());
			newCell.setCellStyle(titileCellStyle);
			// 合并
			if (sheetConfig.getTitleRowCount() > 1)
			{
				titleRowCount = sheetConfig.getTitleRowCount();
				cra = new CellRangeAddress(lastRowNum, lastRowNum + titleRowCount - 1, 0, size - 1);
			}
			else
			{
				cra = new CellRangeAddress(lastRowNum, lastRowNum, 0, size - 1);
			}
			sheet.addMergedRegion(cra);
			setBodyStyle(titileCellStyle, cra, sheet);
			// 设置高度
			int height = workBookConfig.getStyleConfig().getTitleHeight();
			if (height <= 0)
			{
				height = ExportData2Excel.DEFAULTTITLEHEIGHT;
			}
			newRow.setHeight((short) height);
		}

		// 重新计算下一行的行数
		lastRowNum = getNextStartRowNum(sheet);
		// 页眉
		if (!CoreUtil.isEmpty(sheetConfig.getLeftHeader()) || !CoreUtil.isEmpty(sheetConfig.getRightHeader()))
		{
			newRow = sheet.createRow(lastRowNum);
			if (!CoreUtil.isEmpty(sheetConfig.getLeftHeader()))
			{
				newCell = newRow.createCell(0);
				newCell.setCellValue(sheetConfig.getLeftHeader());
				newCell.setCellStyle(leftYmCellStyle);
			}
			if (!CoreUtil.isEmpty(sheetConfig.getRightHeader()))
			{
				newCell = newRow.createCell(size - 1);
				newCell.setCellValue(sheetConfig.getRightHeader());
				newCell.setCellStyle(rightYmCellStyle);
			}
		}
	}

	private void init()
	{
		if (workbook == null)
		{
			if (ExportData2Excel.XLSX.equals(excelType))
			{
				workbook = new XSSFWorkbook();
			}
			else
			{
				workbook = new HSSFWorkbook();
			}
		}
		initCellStyle();
	}

	private void initCellStyle()
	{
		dataCellStyleLeft = workbook.createCellStyle();
		dataCellStyleLeft.setAlignment(CellStyle.ALIGN_LEFT);

		bodyfont = null;
		if (!CoreUtil.isEmpty(workBookConfig.getStyleConfig().getBodyFont()))
		{
			bodyfont = workbook.createFont();
			bodyfont.setFontName(workBookConfig.getStyleConfig().getBodyFont());
		}
		if (workBookConfig.getStyleConfig().getBodyFontSize() > 0)
		{
			if (bodyfont == null)
			{
				bodyfont = workbook.createFont();
			}
			bodyfont.setFontHeightInPoints((short) workBookConfig.getStyleConfig().getBodyFontSize());
		}

		redfont = workbook.createFont();
		if (!CoreUtil.isEmpty(workBookConfig.getStyleConfig().getBodyFont()))
		{
			redfont.setFontName(workBookConfig.getStyleConfig().getBodyFont());
		}
		if (!CoreUtil.isEmpty(workBookConfig.getStyleConfig().getBodyFontSize()))
		{
			int fontSize = CoreUtil.parseInt(workBookConfig.getStyleConfig().getBodyFontSize());
			if (fontSize != 0)
			{
				redfont.setFontHeightInPoints((short) fontSize);
			}
		}
		redfont.setColor(HSSFColor.RED.index);

		// ==============增加边框================
		dataCellStyleLeft.setBorderTop(CellStyle.BORDER_THIN);
		dataCellStyleLeft.setBorderLeft(CellStyle.BORDER_THIN);
		dataCellStyleLeft.setBorderBottom(CellStyle.BORDER_THIN);
		dataCellStyleLeft.setBorderRight(CellStyle.BORDER_THIN);
		dataCellStyleLeft.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		dataCellStyleLeft.setWrapText(true);
		if (bodyfont != null)
		{
			dataCellStyleLeft.setFont(bodyfont);
		}

		dataCellStyleCenter = workbook.createCellStyle();
		dataCellStyleCenter.setAlignment(CellStyle.ALIGN_CENTER);
		// ==============增加边框================
		dataCellStyleCenter.setBorderTop(CellStyle.BORDER_THIN);
		dataCellStyleCenter.setBorderLeft(CellStyle.BORDER_THIN);
		dataCellStyleCenter.setBorderBottom(CellStyle.BORDER_THIN);
		dataCellStyleCenter.setBorderRight(CellStyle.BORDER_THIN);
		dataCellStyleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		dataCellStyleCenter.setWrapText(true);
		if (bodyfont != null)
		{
			dataCellStyleCenter.setFont(bodyfont);
		}

		dataCellStyleRight = workbook.createCellStyle();
		dataCellStyleRight.setAlignment(CellStyle.ALIGN_RIGHT);
		// ==============增加边框================
		dataCellStyleRight.setBorderTop(CellStyle.BORDER_THIN);
		dataCellStyleRight.setBorderLeft(CellStyle.BORDER_THIN);
		dataCellStyleRight.setBorderBottom(CellStyle.BORDER_THIN);
		dataCellStyleRight.setBorderRight(CellStyle.BORDER_THIN);
		dataCellStyleRight.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		dataCellStyleRight.setWrapText(true);
		if (bodyfont != null)
		{
			dataCellStyleRight.setFont(bodyfont);
		}

		// left
		leftYmCellStyle = workbook.createCellStyle();
		leftYmCellStyle.setAlignment(CellStyle.ALIGN_LEFT);

		// right
		rightYmCellStyle = workbook.createCellStyle();
		rightYmCellStyle.setAlignment(CellStyle.ALIGN_RIGHT);

		// title

		Font titlefont = workbook.createFont();
		if (!CoreUtil.isEmpty(workBookConfig.getStyleConfig().getTitleFont()))
		{
			titlefont.setFontName(workBookConfig.getStyleConfig().getTitleFont());
		}
		else
		{
			titlefont.setFontName(ExportData2Excel.DEFAULTTITLEFONT);
		}
		if (workBookConfig.getStyleConfig().getTitleFontSize() > 0)
		{
			titlefont.setFontHeightInPoints((short) workBookConfig.getStyleConfig().getTitleFontSize());
		}
		else
		{
			titlefont.setFontHeightInPoints(ExportData2Excel.DEFAULTTITLEFONTSIZE);
		}
		if (workBookConfig.getStyleConfig().isTitleBold())
		{
			titlefont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		}

		titileCellStyle = workbook.createCellStyle();
		titileCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		titileCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		titileCellStyle.setFont(titlefont);

		// header
		Font headerfont = workbook.createFont();
		if (!CoreUtil.isEmpty(workBookConfig.getStyleConfig().getHeaderFont()))
		{
			headerfont.setFontName(workBookConfig.getStyleConfig().getHeaderFont());
		}
		else
		{
			headerfont.setFontName(ExportData2Excel.DEFAULTHEADERFONT);
		}
		if (workBookConfig.getStyleConfig().getHeaderFontSize() > 0)
		{
			headerfont.setFontHeightInPoints((short) workBookConfig.getStyleConfig().getHeaderFontSize());
		}
		else
		{
			headerfont.setFontHeightInPoints(ExportData2Excel.DEFAULTHEADERFONTSIZE);
		}
		if (workBookConfig.getStyleConfig().isHeaderBold())
		{
			headerfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		}
		headerCellStyle = workbook.createCellStyle();
		headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headerCellStyle.setFont(headerfont);
		// ==============增加边框================
		headerCellStyle.setBorderTop(CellStyle.BORDER_THIN);
		headerCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
		headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		headerCellStyle.setBorderRight(CellStyle.BORDER_THIN);
		headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headerCellStyle.setWrapText(true);

	}

	private void megreRegularHeader(List<? extends HeaderConfig> headerNodes, Sheet sheet, int lastRowNum)
	{
		// 合并单元格
		CellRangeAddress cra = null;
		for (HeaderConfig headerNode : headerNodes)
		{
			// 2016 10 28 提供是否显示选择
			if (!headerNode.isDisplay())
			{
				continue;
			}
			if (headerNode.getRowspan() > 1 || headerNode.getColspan() > 1)
			{
				cra = new CellRangeAddress(headerNode.getRowIndex() + lastRowNum, headerNode.getRowIndex() + headerNode.getRowspan() + lastRowNum - 1, headerNode.getColIndex(), headerNode.getColIndex() + headerNode.getColspan() - 1);
				sheet.addMergedRegion(cra);
				// 设置合并单元格样式
				setBodyStyle(headerCellStyle, cra, sheet);
			}
			if (headerNode.getChildren().size() > 0)
			{
				megreRegularHeader(headerNode.getChildren(), sheet, lastRowNum);
			}
		}
	}

	/**
	 * 获得样式
	 * @param cloneStyle
	 * @param cra
	 * @param sheet
	 */
	private void setBodyStyle(CellStyle cloneStyle, CellRangeAddress cra, Sheet sheet)
	{
		Row rowtemp = null;
		Cell celltemp = null;
		for (int i = cra.getFirstRow(); i <= cra.getLastRow(); i++)
		{
			rowtemp = CellUtil.getRow(i, sheet);
			for (int j = cra.getFirstColumn(); j <= cra.getLastColumn(); j++)
			{
				celltemp = rowtemp.getCell(j);
				if (celltemp == null)
				{
					celltemp = rowtemp.createCell(j);
				}
				celltemp.setCellStyle(cloneStyle);
			}
		}
	}

	private void setColWidth(Sheet sheet, List<HeaderConfig> leafConfigs)
	{
		if (leafConfigs == null || leafConfigs.isEmpty())
		{
			return;
		}
		int index = 0;
		int width = 0;
		for (HeaderConfig headerConfig : leafConfigs)
		{
			width = headerConfig.getWidth();
			if (width == 0)
			{
				// 默认宽度
				width = 6000;
			}
			sheet.setColumnWidth(index, width);
			index++;
		}
	}

	/**
	 * 获取sheet的下一个开始行
	 * @param sheet
	 * @return
	 */
	private int getNextStartRowNum(Sheet sheet)
	{
		if (sheet.getPhysicalNumberOfRows() > 0)
		{
			return sheet.getLastRowNum() + 1;
		}
		else
		{
			return sheet.getLastRowNum();
		}
	}

	/**
	 * 建造
	 * @author cuibo
	 */
	public static class Builder
	{
		private String configId;
		private WorkBookConfig workBookConfig;
		private Map<String, List<?>> allObjectData;
		private Workbook workbook;
		private String excelType;

		public Builder setConfigId(String configId)
		{
			this.configId = configId;
			return this;
		}

		public Builder setWorkBookConfig(WorkBookConfig workBookConfig)
		{
			this.workBookConfig = workBookConfig;
			return this;
		}

		public Builder setAllObjectData(Map<String, List<?>> allObjectData)
		{
			this.allObjectData = allObjectData;
			return this;
		}

		public Builder setWorkbook(Workbook workbook)
		{
			this.workbook = workbook;
			return this;
		}

		public Builder setExcelType(String excelType)
		{
			this.excelType = excelType;
			return this;
		}

		public ExportData2Excel build()
		{
			return new ExportData2Excel(this);
		}
	}

}
